/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mapper;
import dto.ProductDTO;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import utilities.ConnectDB;

/**
 *
 * @author Tam Nguyen
 */
public class ProductMapper {
    
    public ArrayList<dto.ProductDTO> LoadProduct( int category_id ) throws SQLException
    {   
        ArrayList<dto.ProductDTO> ds = new ArrayList<ProductDTO>();
        try
        {

        String qry = "Select * from Product where category_id = "+category_id;
        ResultSet rs = ConnectDB.getResultSet(qry);
        while(rs != null && rs.next())
            {
                dto.ProductDTO sp = new ProductDTO();
                sp.setProduct_id(rs.getString(1));
                sp.setCategory_id(rs.getString(2)) ;
                sp.setProduct_name(rs.getString(3)) ;
                sp.setUnit_price(rs.getInt(4));
                sp.setDescript(rs.getString(5));
                sp.setDiscount(rs.getDouble(6));
                sp.setQuantity(rs.getInt(7));
                sp.setImg(rs.getString(8));
                sp.setImg_desc1(rs.getString(9));
                sp.setImg_desc2(rs.getString(10));
                sp.setImg_desc3(rs.getString(11));
                sp.setProduct_status(rs.getString(12));
                
                ds.add(sp);
            }
        
        }
        catch(Exception e)
        {
        }
        return ds;
    }

    public ProductDTO LoadDetailsProduct( String product_id ) throws SQLException
    {
        ProductDTO ds = new ProductDTO();
        try
        {

        String qry = "Select * from Product where category_id = "+product_id;
        ResultSet rs = ConnectDB.getResultSet(qry);
        if(rs != null && rs.next())
            {
                dto.ProductDTO sp = new ProductDTO();
                sp.setProduct_id(rs.getString(1));
                sp.setCategory_id(rs.getString(2)) ;
                sp.setProduct_name(rs.getString(3)) ;
                sp.setUnit_price(rs.getInt(4));
                sp.setDescript(rs.getString(5));
                sp.setDiscount(rs.getDouble(6));
                sp.setQuantity(rs.getInt(7));
                sp.setImg(rs.getString(8));
                sp.setImg_desc1(rs.getString(9));
                sp.setImg_desc2(rs.getString(10));
                sp.setImg_desc3(rs.getString(11));
                sp.setProduct_status(rs.getString(12));

            }

        }
        catch(Exception e)
        {
        }
        return ds;
    }

    public ArrayList<dto.ProductDTO> LoadAllProduct() throws SQLException
    {   
        ArrayList<dto.ProductDTO> ds = new ArrayList<ProductDTO>();
        try
        {

        String qry = "Select * from Product";
        ResultSet rs = ConnectDB.getResultSet(qry);
        while(rs != null && rs.next())
            {
                dto.ProductDTO sp = new ProductDTO();
                sp.setProduct_id(rs.getString(1));
                sp.setCategory_id(rs.getString(2)) ;
                sp.setProduct_name(rs.getString(3)) ;
                sp.setUnit_price(rs.getInt(4));
                sp.setDescript(rs.getString(5));
                sp.setDiscount(rs.getDouble(6));
                sp.setQuantity(rs.getInt(7));
                sp.setImg(rs.getString(8));
                sp.setImg_desc1(rs.getString(9));
                sp.setImg_desc2(rs.getString(10));
                sp.setImg_desc3(rs.getString(11));
                sp.setProduct_status(rs.getString(12));
                
                ds.add(sp);
            }
        
        }
        catch(Exception e)
        {
        }
        return ds;
    }
    
    public dto.ProductDTO GetOneProduct(String ID) throws SQLException
    {
        String qry = "Select * from Product where product_id = "+ID;
        dto.ProductDTO sp = new ProductDTO();
        ResultSet rs = ConnectDB.getResultSet(qry);
        if(rs!=null && rs.next())
        {
                sp.setProduct_id(rs.getString(1));
                sp.setCategory_id(rs.getString(2)) ;
                sp.setProduct_name(rs.getString(3)) ;
                sp.setUnit_price(rs.getInt(4));
                sp.setDescript(rs.getString(5));
                sp.setDiscount(rs.getDouble(6));
                sp.setQuantity(rs.getInt(7));
                sp.setImg(rs.getString(8));
                sp.setImg_desc1(rs.getString(9));
                sp.setImg_desc2(rs.getString(10));
                sp.setImg_desc3(rs.getString(11));
                sp.setProduct_status(rs.getString(12));
        }
        return sp;
    }
    
    public boolean AddProduct(ProductDTO pr) throws SQLException
    {
        String qry = "Insert into Product values ('"+pr.getProduct_id()+"','"+pr.getCategory_id()+"','"+pr.getProduct_name()+"','"+pr.getUnit_price()+"','"+pr.getDescript()+"','"+pr.getDiscount()+"','"+pr.getQuantity()+"','a','b','c','d','"+pr.getProduct_status()+"')";
        
        
        int i = ConnectDB.executeQueryString(qry);
        if(i == 1 )
            return true;
        return false;
        
    }
    
    public boolean DeleteProduct(String prid) throws SQLException
    {
        String qry = "delete from Product where product_id ="+prid ;
        
        int i = ConnectDB.executeQueryString(qry);
        if(i == 1 )
            return true;
        return false;
        
    }
    
    public boolean UpdateProduct(ProductDTO pr) throws SQLException
    {
        String qry="Update Product set category_id ='"+pr.getCategory_id()+"',product_name='"+pr.getProduct_name()+"',unit_price = '"+pr.getUnit_price()+"' ,descript = '"+pr.getDescript()+"',discount = '"+pr.getDiscount()+"', quantity = '"+pr.getQuantity()+"', product_status = '"+pr.getProduct_status()+"' where product_id = "+pr.getProduct_id();
                 int flag = ConnectDB.executeQueryString(qry);
        if(flag == 1)
        {
            return true;

        }
        else
        {
            return false;
        }
        
    }
    
}
